﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DevExpress.Web.ASPxGridView;

namespace UdrinkWater_Project.Admin
{
    public partial class InsertTimeEmp : System.Web.UI.Page
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }
     
        public static object GetDataValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            return value;
        }
        public void InsertsIN(string ID) 
        {
            string sqltext = @"insert into TB_EmpCheckIn 
                                   ( [Emp_Date]
                                    ,[CheckIn_Date]
                                    ,[CheckIn_Time]
                                    ,[Employee_ID])
                                   values (@Emp_Date,@CheckIn_Date,@CheckIn_Time,@Empoyee_ID)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = dateEdit.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckIn_date", GetDataValue(dateEdit.Value));
            sqlcomm.Parameters.AddWithValue("@CheckIn_Time", GetDataValue(dateEdit.Value));
            
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public void InsertsIN_Nocheck(string ID,int Status)
        {
            string sqltext = @"insert into TB_EmpCheckIn 
                                    ([Emp_Date]
                                    ,[CheckIn_Date]
                                    ,[Employee_ID]
                                    ,[Status_Check])
                                   values (@Emp_Date,@CheckIn_Date,@Empoyee_ID,@Status_Check)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = dateEdit.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckIn_date", GetDataValue(dateEdit.Value));
            sqlcomm.Parameters.AddWithValue("@Status_Check", GetDataValue(Status));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public void InsertsINOT(string ID)
        {
            string sqltext = @"insert into TB_EmployeeOT_IN 
                                    ([Emp_Date]
                                    ,[CheckIN_Date]
                                    ,[CheckIn_Time]
                                    ,[Employee_ID])
                                   values (@Emp_Date,@CheckIn_Date,@CheckIn_Time,@Empoyee_ID)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = dateEdit.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckIn_Date", GetDataValue(dateEdit.Value));
            sqlcomm.Parameters.AddWithValue("@CheckIn_Time", GetDataValue(dateEdit.Value));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public void InsertsOutOT(string ID)
        {
            string sqltext = @"insert into TB_EmployeeOT_Out 
                                   ( [Emp_Date]
                                    ,[CheckOut_Date]
                                    ,[CheckOut_Time]
                                    ,[Employee_ID])
                                   values (@Emp_Date,@CheckOut_Date,@CheckOut_Time,@Empoyee_ID)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = DateEdit_Find.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckOut_Date", GetDataValue(DateEdit_Find.Value));
            string[] Time = TimeEdit_CheckOut.Value.ToString().Split(' '); 
            sqlcomm.Parameters.AddWithValue("@CheckOut_Time", GetDataValue(Time[1]));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public void InsertsOut(string ID)
        {
            string sqltext = @"insert into TB_EmpCheckOut 
                                   ( [Emp_Date]
                                    ,[CheckOut_date]
                                    ,[CheckOut_time]
                                    ,[Employee_ID])
                                   values (@Emp_Date,@CheckOut_Date,@CheckOut_Time,@Empoyee_ID)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = DateEdit_Find.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckOut_Date", GetDataValue(DateEdit_Find.Value));
            string[] Time = TimeEdit_CheckOut.Value.ToString().Split(' '); 
            sqlcomm.Parameters.AddWithValue("@CheckOut_Time", GetDataValue(Time[1]));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        public void InsertsOut_Nocheck(string ID)
        {
            string sqltext = @"insert into TB_EmpCheckOut 
                                   ([Emp_Date]
                                    ,[CheckOut_date]
                                    ,[Employee_ID])
                                   values (@Emp_Date,@CheckOut_Date,@Empoyee_ID)";
            SqlCommand sqlcomm = new SqlCommand();
            SqlConnection sqlconn = new SqlConnection(Connection);
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = System.Data.CommandType.Text;
            sqlcomm.CommandText = sqltext;
            string[] Date = dateEdit.Value.ToString().Split(' ');
            sqlcomm.Parameters.AddWithValue("@Emp_Date", GetDataValue(ID+Date[0]));
            sqlcomm.Parameters.AddWithValue("@Empoyee_ID", GetDataValue(ID));
            sqlcomm.Parameters.AddWithValue("@CheckOut_Date", GetDataValue(dateEdit.Value));

            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }

        protected void btOK_Click(object sender, EventArgs e)
        {
            string date = dateEdit.Value.ToString();
            string[] subdate = date.Split(' ');
            string DateNow = Convert.ToDateTime(subdate[0]).ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
            if (listbox_timecheck.SelectedIndex == 1)
            {
                DataTable dt = new DataTable();
                dt = GetData_CheckOTIn(DateNow);
                if (dt.Rows.Count == 0)
                {
                    for (int i = 0; i < GV_ListEmp.VisibleRowCount; i++)
                    {
                        if (GV_ListEmp.Selection.IsRowSelected(i) == true)
                        {
                            object val = GV_ListEmp.GetRowValues(i, "ID_Card");
                            InsertsINOT(val.ToString());
                        }
                        else
                        {
                            
                        }

                    }
                    GV_ListEmp.Selection.UnselectAll();
                }
                else
                {
                    DataTable checktime = new DataTable();
                    checktime = GetData_CheckOTOut(DateNow);
                    if (checktime.Rows.Count == 0)
                    {
                        for (int i = 0; i < GV_ListEmp.VisibleRowCount; i++)
                        {
                            if (GV_ListEmp.Selection.IsRowSelected(i) == true)
                            {
                                object val = GV_ListEmp.GetRowValues(i, "ID_Card");
                                InsertsOutOT(val.ToString());
                            }
                            else
                            {
                                
                            }

                        }
                        GV_ListEmp.Selection.UnselectAll();
                        
                    }
                    else
                    {
                        //popup error
                    }
                }
            }
            else
            {
                DataTable dt = new DataTable();
                dt = GetData_CheckIn(DateNow);
                if (dt.Rows.Count == 0)
                {
                    for (int i = 0; i < GV_ListEmp.VisibleRowCount; i++)
                    {
                        object val = GV_ListEmp.GetRowValues(i, "ID_Card");
                        if (GV_ListEmp.Selection.IsRowSelected(i) == true)
                        {
                            InsertsIN(val.ToString());
                        }
                        else
                        {
                            DataTable Check_Status = new DataTable();
                            Check_Status = GetData_Check_Status();
                            if (Check_Status.Rows.Count < 2)
                            {
                                InsertsIN_Nocheck(val.ToString(),1);
                                InsertsOut_Nocheck(val.ToString());
                            }
                            else
                            {
                                InsertsIN_Nocheck(val.ToString(),2);
                                InsertsOut_Nocheck(val.ToString());
                            }
                        }

                    }
                    GV_ListEmp.Selection.UnselectAll();
                    Session["DateNow"] = DateTime.Now.ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
                    GV_ListEmpOut.DataBind();
                    
                }
                //เงื่อนไข Else
            }
           
        }
        public DataTable GetData_Check_Status()
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT * FROM [TB_EmpCheckIn] WHERE CheckIn_Date BETWEEN CONVERT(DATE,DATEADD(s,+1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+0,0))) 
                               AND CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) AND Status_Check=1";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
      
        public DataTable GetData_CheckIn(string DateNow)
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT * FROM TB_EmpCheckIn WHERE [CheckIn_Date] = '"+DateNow+"' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_CheckOut(string DateNow)
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT * FROM TB_EmpCheckOut WHERE [CheckOut_date] = '" + DateNow + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_CheckOTIn(string DateNow)
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT * FROM TB_EmployeeOT_IN WHERE [CheckIn_Date] = '" + DateNow + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        public DataTable GetData_CheckOTOut(string DateNow)
        {
            DataTable dt = new DataTable();
            string sqltext = @"SELECT * FROM TB_EmployeeOT_Out WHERE [CheckOut_date] = '" + DateNow + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        protected void bt_Confirm_Out_Click(object sender, EventArgs e)
        {
            string date = DateEdit_Find.Value.ToString();
            string[] subdate = date.Split(' ');
            string DateNow = Convert.ToDateTime(subdate[0]).ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
            if (listbox_timecheckOut.SelectedIndex == 1)
            {
                DataTable dt = new DataTable();
                dt = GetData_CheckOTIn(DateNow);
                if (dt.Rows.Count == 0)
                {
                    for (int i = 0; i < GV_ListEmp.VisibleRowCount; i++)
                    {
                        if (GV_ListEmp.Selection.IsRowSelected(i) == true)
                        {
                            object val = GV_ListEmp.GetRowValues(i, "ID_Card");

                            InsertsINOT(val.ToString());
                        }

                    }
                    GV_ListEmpOut.Selection.UnselectAll();
                }
                else
                {
                    DataTable checktime = new DataTable();
                    checktime = GetData_CheckOTOut(DateNow);
                    if (checktime.Rows.Count == 0)
                    {
                        for (int i = 0; i < GV_ListEmp.VisibleRowCount; i++)
                        {
                            if (GV_ListEmp.Selection.IsRowSelected(i) == true)
                            {
                                object val = GV_ListEmp.GetRowValues(i, "ID_Card");

                                InsertsOutOT(val.ToString());
                            }

                        }
                        GV_ListEmpOut.Selection.UnselectAll();
                    }
                    else
                    {
                        //popup error
                    }
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt = GetData_CheckIn(DateNow);
                if (dt.Rows.Count != 0)
                {
                    for (int i = 0; i < GV_ListEmpOut.VisibleRowCount; i++)
                    {
                        if (GV_ListEmpOut.Selection.IsRowSelected(i) == true)
                        {
                            object val = GV_ListEmpOut.GetRowValues(i, "ID_Card");

                            InsertsOut(val.ToString());

                        }

                    }
                    GV_ListEmpOut.Selection.UnselectAll();
                }
                else
                {
                    //popup Error
                }
            }
        }

        protected void bt_TimeFind_Click(object sender, EventArgs e)
        {
            string DateFind = Convert.ToDateTime(DateEdit_Find.Value).ToString("yyyy-MM-dd", new System.Globalization.CultureInfo("en-US"));
            Session["DateNow"] = DateFind;
        }

       

    }
}
            